package com.practice.springboot_01_zwj.scheduled;

// FileImporterService.java 文件导入服务类
import com.practice.springboot_01_zwj.entity.HealthyEightRate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.lang.reflect.Method;
import java.nio.charset.StandardCharsets;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
public class EightRateImporterService {

    private static final Logger logger = LoggerFactory.getLogger(EightRateImporterService.class);
    private static final int BATCH_SIZE = 2000;
    private static final int REQUIRED_FIELD_COUNT = 13;

    // 字段索引常量
    private static final int PROVINCE_CODE = 0;
    private static final int CITY_CODE = 1;
    private static final int CITY_NAME = 2;
    private static final int PROFIT_TOTAL = 3;
    private static final int SELF_IMPLEMENTATION = 4;
    private static final int PROFIT_UNIVERSAL = 5;
    private static final int PROPRIETARY_PRODUCT = 6;
    private static final int SELF_DEVELOPED_PRODUCT = 7;
    private static final int PROMPT_DELIVERY = 8;
    private static final int LEAD_OPPORTUNITY = 9;
    private static final int RECEIPT_PERCENT = 10;
    private static final int UNVERIFIED_PERCENT = 11;
    private static final int CONTROL_ABILITY = 12;

    // 存储数据用于校验
    private HealthyEightRate heilongjiangData = null;
    private List<HealthyEightRate> cityDataList = new ArrayList<>();

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 主导入方法
     */
    @Transactional
    public void importLargeFile(String filePath) {
        long startTime = System.currentTimeMillis();
        logger.info("开始导入文件：{}", filePath);

        // 清空缓存数据
        heilongjiangData = null;
        cityDataList.clear();

        // 删除原有数据
        logger.info("开始删除表中原有数据...");
        long deleteStart = System.currentTimeMillis();
        int deletedRows = jdbcTemplate.update("DELETE FROM gzt_healthy_eight_rate");
        logger.info("已删除 {} 条原有数据，耗时：{}ms", deletedRows, System.currentTimeMillis() - deleteStart);

        List<HealthyEightRate> buffer = new ArrayList<>(BATCH_SIZE);
        int totalLines = 0;
        int successCount = 0;
        boolean isFirstLine = true;

        try (BufferedReader br = new BufferedReader(
                new InputStreamReader(new FileInputStream(filePath), StandardCharsets.UTF_8), 16384)) {

            String line;
            while ((line = br.readLine()) != null) {
                if(isFirstLine){
                    isFirstLine = false;
                    continue;
                }
                totalLines++;
                try {
                    HealthyEightRate entity = parseLine(line, totalLines);
                    if (entity != null) {
                        buffer.add(entity);
                        successCount++;

                        // 缓存数据用于校验
                        cacheDataForValidation(entity);
                    }

                    if (buffer.size() >= BATCH_SIZE) {
                        executeBatchInsert(buffer);
                        buffer.clear();
                    }
                } catch (Exception e) {
                    logger.error("第 {} 行解析失败：{} | 原始行：{}",
                            totalLines, e.getMessage(), truncateString(line, 100));
                }
            }

            if (!buffer.isEmpty()) {
                executeBatchInsert(buffer);
            }

            // 执行数据校验
            performDataValidation();

        } catch (Exception e) {
            logger.error("文件读取失败", e);
            throw new RuntimeException("文件导入失败", e);
        }

        logger.info("导入完成，总行数：{}，成功：{}，耗时：{}ms",
                totalLines, successCount, System.currentTimeMillis() - startTime);
    }

    /**
     * 缓存数据用于后续校验
     */
    private void cacheDataForValidation(HealthyEightRate entity) {
        String cityName = entity.getCityName();

        if ("全省".equals(cityName)) {
            heilongjiangData = entity;
            logger.info("找到全省数据: provinceCode={}, cityCode={}",
                    entity.getProvinceCode(), entity.getCityCode());
        } else {
            cityDataList.add(entity);
        }
    }

    /**
     * 执行数据校验
     */
    private void performDataValidation() {
        logger.info("开始执行数据校验...");

        if (heilongjiangData == null) {
            logger.warn("未找到全省数据，跳过校验");
            return;
        }

        if (cityDataList.isEmpty()) {
            logger.warn("未找到地市数据，跳过校验");
            return;
        }

        logger.info("全省数据: {}", heilongjiangData);
        logger.info("地市数据数量: {}", cityDataList.size());

        int warningCount = 0;

        // 检查所有指标
        warningCount += checkIndicator("profitTotal",
                heilongjiangData.getProfitTotal(),
                "利润总额");

        warningCount += checkIndicator("selfImplementation",
                heilongjiangData.getSelfImplementation(),
                "自实施");

        warningCount += checkIndicator("profitUniversal",
                heilongjiangData.getProfitUniversal(),
                "通用利润");

        warningCount += checkIndicator("proprietaryProduct",
                heilongjiangData.getProprietaryProduct(),
                "专有产品");

        warningCount += checkIndicator("selfDevelopedProduct",
                heilongjiangData.getSelfDevelopedProduct(),
                "自研产品");

        warningCount += checkIndicator("promptDelivery",
                heilongjiangData.getPromptDelivery(),
                "及时交付");

        warningCount += checkIndicator("leadOpportunity",
                heilongjiangData.getLeadOpportunity(),
                "商机引领");

        warningCount += checkIndicator("receiptPercent",
                heilongjiangData.getReceiptPercent(),
                "回款率");

        warningCount += checkIndicator("unverifiedPercent",
                heilongjiangData.getUnverifiedPercent(),
                "未核实率");

        warningCount += checkIndicator("controlAbility",
                heilongjiangData.getControlAbility(),
                "管控能力");

        if (warningCount == 0) {
            logger.info("数据校验完成，未发现异常情况");
        } else {
            logger.warn("数据校验完成，共发现 {} 个预警情况", warningCount);
        }
    }

    /**
     * 检查单个指标
     */
    private int checkIndicator(String fieldName, Double provinceValue, String displayName) {
        int warnings = 0;

        try {
            // 情况1：省指标>0，但地市对应指标全为0或null或-
            if (isGreaterThanZero(provinceValue)) {
                boolean allCityZero = true;
                List<String> zeroCities = new ArrayList<>();

                for (HealthyEightRate city : cityDataList) {
                    Double cityValue = getFieldValue(city, fieldName);
                    if (isGreaterThanZero(cityValue)) {
                        allCityZero = false;
                        break;
                    } else {
                        zeroCities.add(city.getCityName());
                    }
                }

                if (allCityZero && !cityDataList.isEmpty()) {
                    logger.warn("【数据预警】{}指标异常：全省={}，但所有地市该指标均为0/null/-",
                            displayName, formatValue(provinceValue));
                    warnings++;
                }
            }

            // 情况2：省指标=0或null或-，但至少有一个地市指标>0
            if (!isGreaterThanZero(provinceValue)) {
                List<String> problemCities = new ArrayList<>();

                for (HealthyEightRate city : cityDataList) {
                    Double cityValue = getFieldValue(city, fieldName);
                    if (isGreaterThanZero(cityValue)) {
                        problemCities.add(String.format("%s(%s)",
                                city.getCityName(), formatValue(cityValue)));
                    }
                }

                if (!problemCities.isEmpty()) {
                    logger.warn("【数据预警】{}指标异常：全省={}，但以下地市该指标>0：{}",
                            displayName, formatValue(provinceValue),
                            String.join("、", problemCities));
                    warnings++;
                }
            }

        } catch (Exception e) {
            logger.error("校验指标{}时发生错误：{}", displayName, e.getMessage());
        }

        return warnings;
    }

    /**
     * 通过反射获取字段值
     */
    private Double getFieldValue(HealthyEightRate entity, String fieldName) {
        try {
            String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
            Method method = entity.getClass().getMethod(methodName);
            Object value = method.invoke(entity);
            return value instanceof Double ? (Double) value : null;
        } catch (Exception e) {
            logger.error("获取字段{}值失败：{}", fieldName, e.getMessage());
            return null;
        }
    }

    /**
     * 判断值是否大于0（处理0、null和-的情况）
     */
    private boolean isGreaterThanZero(Double value) {
        return value != null && value > 0;
    }

    /**
     * 格式化数值显示
     */
    private String formatValue(Double value) {
        if (value == null) return "null";
        return String.format("%.2f", value);
    }

    /**
     * 增强数值转换方法：处理0、null和-的情况
     */
    private Double safeParseDouble(String value) {
        if (value == null || value.trim().isEmpty() || "-".equals(value.trim()) || "null".equalsIgnoreCase(value.trim())) {
            return 0.0; // 将null、空字符串、"-"和"null"都转换为0
        }

        try {
            // 移除数值中的非数字字符（保留小数点和负号）
            String cleanValue = value.replaceAll("[^\\d.-]", "");
            if (cleanValue.isEmpty() || "-".equals(cleanValue)) {
                return 0.0;
            }
            return Double.parseDouble(cleanValue);
        } catch (NumberFormatException e) {
            logger.warn("数值转换失败，将视为0：{}", value);
            return 0.0; // 转换失败也返回0
        }
    }

    /**
     * 字符串处理（保留null，但将"-"转换为null）
     */
    private String parseString(String value) {
        if (value == null || value.trim().isEmpty() || "-".equals(value.trim())) {
            return null;
        }
        return value.equalsIgnoreCase("null") ? null : value;
    }

    /**
     * 字段提取方法
     */
    private List<String> extractFields(String line) {
        List<String> fields = new ArrayList<>(REQUIRED_FIELD_COUNT);
        String[] parts = line.split("\\|", -1);

        for (String part : parts) {
            String field = part.replaceAll("^\"|\"$", "").trim();
            fields.add(field.isEmpty() ? null : field);
        }

        while (fields.size() < REQUIRED_FIELD_COUNT) {
            fields.add(null);
        }

        return fields.subList(0, REQUIRED_FIELD_COUNT);
    }

    /**
     * 解析单行数据
     */
    private HealthyEightRate parseLine(String line, int lineNumber) {
        if (line.trim().isEmpty()) {
            logger.warn("第 {} 行为空行，已跳过", lineNumber);
            return null;
        }

        List<String> fields = extractFields(line);

        if (fields.size() != REQUIRED_FIELD_COUNT) {
            throw new IllegalArgumentException(
                    String.format("字段数量错误（实际：%d，需要：%d）", fields.size(), REQUIRED_FIELD_COUNT));
        }

        HealthyEightRate entity = new HealthyEightRate();
        try {
            entity.setProvinceCode(parseString(fields.get(PROVINCE_CODE)));
            entity.setCityCode(parseString(fields.get(CITY_CODE)));
            entity.setCityName(parseString(fields.get(CITY_NAME)));
            entity.setProfitTotal(safeParseDouble(fields.get(PROFIT_TOTAL)));
            entity.setSelfImplementation(safeParseDouble(fields.get(SELF_IMPLEMENTATION)));
            entity.setProfitUniversal(safeParseDouble(fields.get(PROFIT_UNIVERSAL)));
            entity.setProprietaryProduct(safeParseDouble(fields.get(PROPRIETARY_PRODUCT)));
            entity.setSelfDevelopedProduct(safeParseDouble(fields.get(SELF_DEVELOPED_PRODUCT)));
            entity.setPromptDelivery(safeParseDouble(fields.get(PROMPT_DELIVERY)));
            entity.setLeadOpportunity(safeParseDouble(fields.get(LEAD_OPPORTUNITY)));
            entity.setReceiptPercent(safeParseDouble(fields.get(RECEIPT_PERCENT)));
            entity.setUnverifiedPercent(safeParseDouble(fields.get(UNVERIFIED_PERCENT)));
            entity.setControlAbility(safeParseDouble(fields.get(CONTROL_ABILITY)));

            // 调试日志：显示解析后的关键信息
            if (logger.isDebugEnabled()) {
                logger.debug("第{}行解析结果：cityName={}, profitTotal={}, controlAbility={}",
                        lineNumber, entity.getCityName(), entity.getProfitTotal(), entity.getControlAbility());
            }

        } catch (Exception e) {
            throw new IllegalArgumentException("字段映射失败：" + e.getMessage());
        }
        return entity;
    }

    @Transactional(propagation = Propagation.REQUIRED)
    void executeBatchInsert(List<HealthyEightRate> entities) {
        String sql = "INSERT INTO gzt_healthy_eight_rate (" +
                "provinceCode, cityCode, cityName, " +
                "profitTotal, selfImplementation, profitUniversal, " +
                "proprietaryProduct, selfDevelopedProduct, promptDelivery, " +
                "leadOpportunity, receiptPercent, unverifiedPercent, controlAbility" +
                ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                HealthyEightRate entity = entities.get(i);
                int index = 1;
                ps.setString(index++, entity.getProvinceCode());
                ps.setString(index++, entity.getCityCode());
                ps.setString(index++, entity.getCityName());
                setNullableDouble(ps, index++, entity.getProfitTotal());
                setNullableDouble(ps, index++, entity.getSelfImplementation());
                setNullableDouble(ps, index++, entity.getProfitUniversal());
                setNullableDouble(ps, index++, entity.getProprietaryProduct());
                setNullableDouble(ps, index++, entity.getSelfDevelopedProduct());
                setNullableDouble(ps, index++, entity.getPromptDelivery());
                setNullableDouble(ps, index++, entity.getLeadOpportunity());
                setNullableDouble(ps, index++, entity.getReceiptPercent());
                setNullableDouble(ps, index++, entity.getUnverifiedPercent());
                setNullableDouble(ps, index++, entity.getControlAbility());
            }

            @Override
            public int getBatchSize() {
                return entities.size();
            }
        });
    }

    private void setNullableDouble(PreparedStatement ps, int index, Double value) throws SQLException {
        if (value != null) {
            ps.setDouble(index, value);
        } else {
            ps.setNull(index, java.sql.Types.DOUBLE);
        }
    }

    private String truncateString(String input, int maxLength) {
        return input.length() > maxLength ? input.substring(0, maxLength) + "..." : input;
    }
}